#!/usr/bin/python

import MySQLdb
import urllib
import urllib2
import sys
import string
import os
import shutil
from bs4 import BeautifulSoup

def staffDBSetup():

	#Open database connection
	db = MySQLdb.connect("mysql.cs.orst.edu", "cs419_group1", "yuPMS8mTxvbPRn6U", "cs419_group1")

	#Prepare a cursor object using cursor() method
	cursor = db.cursor()

	#Drop table if it already exists using execute() method
	cursor.execute("DROP TABLE IF EXISTS Staff_Sp14")

	#Create table for testing
	staffSetup = """CREATE TABLE `Staff_Sp14` (
		id int(11) NOT NULL AUTO_INCREMENT,
		first_name varchar(40) NOT NULL,
		last_name varchar(40) NOT NULL,
		middle_name varchar(40),
		ONID_ID varchar(40) NOT NULL,
		email varchar(60),
		first_initial varchar(1),
		namePHPSearch varchar(40) NOT NULL,
		PRIMARY KEY (id),
		UNIQUE KEY (ONID_ID)
		) ENGINE = InnoDB;"""

	cursor.execute(staffSetup)


	# files where info will be stored to
	staffList="staffList.txt"
	tempfile="tempfile.txt"
	tempfile2="tempfile2.txt"

	temp = []
	staff = []

	staffList = open(staffList, "r")
	for line in staffList:
		temp1 = line[:-1]
		#print temp1
		if temp1.find('----------') != -1:
			if len(temp) > 0:
				staff.append(temp)
				temp = []
			#print staff
		else:
			temp.append(temp1)
			#print temp

	#print temp
	#print staff
	#print len(staff)

	temp = []

	for x in range(len(staff)):
		if staff[x][0] == None:
			temp.append(x)

	#print len(staff)
	#print temp

	temp.reverse()

	#print len(staff)
	for x in range(len(temp)):
		y = temp[x]
		staff.pop(y)

	temp = []

	#print staff[0:50]

	#print len(staff)
	for x in range(len(staff)):
		try:
			if staff[x][1] != 'Employee':
				temp.append(x)
		except:
			temp.append(x)

	#reverse temp
	temp.reverse()

	for x in range(len(temp)):
		y = temp[x]
		staff.pop(y)

	#print staff
	#print len(staff)

	temp = []

	#check for duplicates
	for x in range(1, len(staff)):
		for y in range(0, len(staff)):
				if x != y:
					if staff[y][0] == staff[x][0] and staff[y][1] == staff[x][1] and staff[y][2] == staff[x][2] and staff[x][3] == staff[y][3]:
						#print x
						temp.append(x)

	#reverse temp
	temp.reverse()

	for x in range(len(temp)):
		y = temp[x]
		staff.pop(y)

	#print staff
	#print len(staff)


	for x in range(len(staff)):
		temp = staff[x][0]
		name_instructor = temp.split(', ')
		staff[x].extend(name_instructor)
		last_name = name_instructor[0]
		first_name_m_i = name_instructor[1]
		try:
			first_name_m_i = first_name_m_i.split(' ')
			staff[x].extend(first_name_m_i)
		except:
			staff[x].extend('12345')
		finally:
			#print x
			a = x

	#print staff

	staff2 = []

	for x in range(len(staff)):
		temp = len(staff[x]) -1
		first_name = staff[x][temp-1]
		middle_name = staff[x][temp]
		if first_name == middle_name:
			middle_name = None
		first_initial = first_name[:1]
		if middle_name != None:
			last_name = staff[x][temp-3]
			ONID_ID = staff[x][temp-4]
			email = staff[x][temp-5]
			#middle_name = 'NULL'
		else:
			last_name = staff[x][temp-2]
			ONID_ID = staff[x][temp-3]
			email = staff[x][temp-4]
		if email.find('@') == -1:
			email = None
			#email = 'NULL'
		temp1 = [first_name, last_name, middle_name, first_initial, ONID_ID, email]
		staff2.append(temp1)
		namePHPSearch = last_name + ', ' + first_initial + '.'
		staffValues = {
			'first_name': first_name,
			'last_name': last_name,
			'middle_name': middle_name,
			'ONID_ID':  ONID_ID,
			'email': email,
			'first_initial': first_initial,
			'namePHPSearch': namePHPSearch,
		}
		addStaff = """INSERT IGNORE INTO `Staff_Sp14` (
				first_name, last_name, middle_name, ONID_ID, email, first_initial, namePHPSearch)
				VALUES (%(first_name)s, %(last_name)s, %(middle_name)s, %(ONID_ID)s, %(email)s, %(first_initial)s, %(namePHPSearch)s);"""
		cursor.execute(addStaff, staffValues)

	db.commit()
	#print staff2






	staffList.close()
	
	return None
